package com.nf.dao;

import com.nf.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao{

    public Student getStudentById(int id) {
        Student stu=null;
        Connection con=new HisConnectionImpl().getConnection();
        String sql="SELECT * FROM student WHERE id = ?";
        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setInt(1,id);
            ResultSet rs =pst.executeQuery();
            while( rs.next()){
                stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setSex(rs.getString("sex"));
                stu.setAge(rs.getInt("age"));
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            try {
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return stu;
    }

    public List<Student> getStudentAll() {
        List<Student> stus=new ArrayList<Student>();
        Connection con=new HisConnectionImpl().getConnection();
        String sql="SELECT * FROM student";
        try {
            PreparedStatement pst=con.prepareStatement(sql);
            ResultSet rs =pst.executeQuery();
            while( rs.next()){
                Student stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setSex(rs.getString("sex"));
                stu.setAge(rs.getInt("age"));
                stus.add(stu);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            try {
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return stus;
    }

    public List<Student> getStudentByName(String name) {
        List<Student> stus=new ArrayList<Student>();
        Connection con=new HisConnectionImpl().getConnection();
        //String sql="SELECT * FROM student WHERE name LIKE"+"'%"+name+"%'";
        String sql="SELECT * FROM student WHERE name LIKE '%'?'%' " ;
        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setString(1,name);
            ResultSet rs =pst.executeQuery();
            while( rs.next()){
                Student stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setSex(rs.getString("sex"));
                stu.setAge(rs.getInt("age"));
                stus.add(stu);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            try {
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return stus;
    }

    public List<Student> getStudentBySex(String sex) {
        List<Student> stus=new ArrayList<Student>();
        Connection con=new HisConnectionImpl().getConnection();
        String sql="SELECT * FROM student WHERE sex = ? ";
        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setString(1,sex);
            ResultSet rs =pst.executeQuery();
            while( rs.next()){
                Student stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setSex(rs.getString("sex"));
                stu.setAge(rs.getInt("age"));
                stus.add(stu);
            }
            rs.close();
            pst.close();
            con.close();
        } catch (SQLException e) {
            try {
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return stus;
    }
}
